Imports Microsoft.VisualBasic
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.Data
Imports DataAccesGenerales
Imports DataAcces

Public Class DataAccesAvisoArribo
	Private Const consTimeOut As Integer = 0 

    ''' <summary>
    ''' Funcion para Insertar un regristro en la tabla AvisoArribo
    ''' </summary>
	Public Function AvisoArribo_InsertarModificar(pnidDocumento As Long, pnidPais_Origen As Integer, pnidPais_Destino As Integer, pnidPuerto_Origen As Integer, pnidPuerto_Destino As Integer, pTRB As Double, peslora As Double, pcalado As Double, pmanga As Double, pTRN As Double, pfecha_ETA As String, pfecha_ETF As String, pnoExisteBuque As String, pletraLlamada As String, pbandera As String, pnidBuque As Integer, pnidViaje As Integer, pnidEmpRemolcador As Integer, pnidEmpPilotos As Integer, pnidCargaImpo As Integer, pnidCargaExpo As Integer, pnidCargaCabEnt As Integer, pnidCargaCabSal As Integer, ptonelaje_Expo As Double, ptonelaje_Impo As Double, ptonelaje_CabEnt As Double, ptonelaje_CabSal As Double, pdescargaXDia As Double, pcargaXDia As Double, pdescargaXDiaCabEnt As Double, pcargaXDiaCabSal As Double, pMMSI As String, pfacturar As String, pnombre_Buque As String, pdescCargaExpo As String, pdescCargaImpo As String, pdescCargaCabEnt As String, pdescCargaCabSal As String, pmotivoCancelacion As String, pobservaciones As String, pcapitan As String, pnum_Serie As String, pnidPais_OrigenCarga As Integer) As Byte
        Dim pTransaccion As Byte = 0
        Dim mensaje As String = String.Empty
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.AvisoArriboInsertarModificar"
          oraCommandDocumentos.Parameters.Add("pnidDocumento", pnidDocumento)
          oraCommandDocumentos.Parameters.Add("pnidPais_Origen", pnidPais_Origen)
          oraCommandDocumentos.Parameters.Add("pnidPais_Destino", pnidPais_Destino)
          oraCommandDocumentos.Parameters.Add("pnidPuerto_Origen", pnidPuerto_Origen)
          oraCommandDocumentos.Parameters.Add("pnidPuerto_Destino", pnidPuerto_Destino)
          oraCommandDocumentos.Parameters.Add("pTRB", pTRB)
          oraCommandDocumentos.Parameters.Add("peslora", peslora)
          oraCommandDocumentos.Parameters.Add("pcalado", pcalado)
          oraCommandDocumentos.Parameters.Add("pmanga", pmanga)
          oraCommandDocumentos.Parameters.Add("pTRN", pTRN)
          oraCommandDocumentos.Parameters.Add("pfecha_ETA", pfecha_ETA)
          oraCommandDocumentos.Parameters.Add("pfecha_ETF", pfecha_ETF)
          oraCommandDocumentos.Parameters.Add("pnoExisteBuque", pnoExisteBuque)
          oraCommandDocumentos.Parameters.Add("pletraLlamada", pletraLlamada)
          oraCommandDocumentos.Parameters.Add("pbandera", pbandera)
          oraCommandDocumentos.Parameters.Add("pnidBuque", pnidBuque)
          oraCommandDocumentos.Parameters.Add("pnidViaje", pnidViaje)
          oraCommandDocumentos.Parameters.Add("pnidEmpRemolcador", pnidEmpRemolcador)
          oraCommandDocumentos.Parameters.Add("pnidEmpPilotos", pnidEmpPilotos)
          oraCommandDocumentos.Parameters.Add("pnidCargaImpo", pnidCargaImpo)
          oraCommandDocumentos.Parameters.Add("pnidCargaExpo", pnidCargaExpo)
          oraCommandDocumentos.Parameters.Add("pnidCargaCabEnt", pnidCargaCabEnt)
          oraCommandDocumentos.Parameters.Add("pnidCargaCabSal", pnidCargaCabSal)
          oraCommandDocumentos.Parameters.Add("ptonelaje_Expo", ptonelaje_Expo)
          oraCommandDocumentos.Parameters.Add("ptonelaje_Impo", ptonelaje_Impo)
          oraCommandDocumentos.Parameters.Add("ptonelaje_CabEnt", ptonelaje_CabEnt)
          oraCommandDocumentos.Parameters.Add("ptonelaje_CabSal", ptonelaje_CabSal)
          oraCommandDocumentos.Parameters.Add("pdescargaXDia", pdescargaXDia)
          oraCommandDocumentos.Parameters.Add("pcargaXDia", pcargaXDia)
          oraCommandDocumentos.Parameters.Add("pdescargaXDiaCabEnt", pdescargaXDiaCabEnt)
          oraCommandDocumentos.Parameters.Add("pcargaXDiaCabSal", pcargaXDiaCabSal)
          oraCommandDocumentos.Parameters.Add("pMMSI", pMMSI)
          oraCommandDocumentos.Parameters.Add("pfacturar", pfacturar)
          oraCommandDocumentos.Parameters.Add("pnombre_Buque", pnombre_Buque)
          oraCommandDocumentos.Parameters.Add("pdescCargaExpo", pdescCargaExpo)
          oraCommandDocumentos.Parameters.Add("pdescCargaImpo", pdescCargaImpo)
          oraCommandDocumentos.Parameters.Add("pdescCargaCabEnt", pdescCargaCabEnt)
          oraCommandDocumentos.Parameters.Add("pdescCargaCabSal", pdescCargaCabSal)
          oraCommandDocumentos.Parameters.Add("pmotivoCancelacion", pmotivoCancelacion)
          oraCommandDocumentos.Parameters.Add("pobservaciones", pobservaciones)
          oraCommandDocumentos.Parameters.Add("pcapitan", pcapitan)
          oraCommandDocumentos.Parameters.Add("pnum_Serie", pnum_Serie)
          oraCommandDocumentos.Parameters.Add("pnidPais_OrigenCarga", pnidPais_OrigenCarga)

                    oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add("pMensaje", Oracle.DataAccess.Client.OracleDbType.Varchar2, 200).Direction = ParameterDirection.Output

                    oraConnection.Open()
                    oraCommandDocumentos.ExecuteNonQuery()
                    pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
                    mensaje = oraCommandDocumentos.Parameters("pMensaje").Value.ToString()
                    oraConnection.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return pTransaccion
    End Function
  
    ''' <summary>
    ''' Funcion para consultar un registro de la tabla AvisoArribo
    ''' </summary>
    ''' <param name="pNidDocumento">numero de documento</param>
    ''' <returns>DataTable</returns>
    ''' <remarks></remarks>
   Public Function AvisoArribo_Consultar(pNidDocumento As Long) As DataTable
        Dim dtResultado As New DataTable("AvisoArriboConsulta")
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.AvisoArriboConsultar"
                    oraCommandDocumentos.Parameters.Add("pNidDocumento", pNidDocumento)
                    oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

                    Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
                    oraDataAdapter.Fill(dtResultado)
                    oraConnection.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return dtResultado
    End Function
  
    ''' <summary>
    ''' Funcion para generar la cadena XML Chilkat del documento de la tabla AvisoArribo
    ''' </summary>
    ''' <param name="nidDocumento"> numero del documento</param>
    ''' <returns>Chilkat.Xml</returns>
    ''' <remarks></remarks>
   Public Function AvisoArribo_CadenaOriginalXMLChilkat(nidDocumento As Long) As Chilkat.Xml
        Dim detalleDataSet As New DataSet("DatosAvisoArribo")
        Try
      detalleDataSet = AvisoArribo_DetalleXML(nidDocumento)
            'En esta parte van las tablas obtenidas en la consulta anterior
            'separadas dependiendo del reporte a crear
            detalleDataSet.Tables(0).TableName = "Informacion del Tramite"
            detalleDataSet.Tables(1).TableName = "Datos AvisoArribo"
            detalleDataSet.Tables(2).TableName = "Archivos Anexos"
            detalleDataSet.Tables(3).TableName = "Incidencias"

            Dim xml As New Chilkat.Xml
            xml.LoadXml(detalleDataSet.GetXml())
            Return xml 'detalleDataSet.GetXml()
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    ''' <summary>
    ''' Funcion para consultar el detalle del documento de la tabla AvisoArribo
    ''' </summary>
    ''' <param name="pNidDocumento">numero del documento</param>
    ''' <returns>DataSet</returns>
    ''' <remarks></remarks>
	Public Function AvisoArribo_DetalleXML(pNidDocumento As Long) As DataSet
        Dim dsDetalle As New DataSet("AvisoArriboDetalle")
        Dim pTransaccion As Byte = 0
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.AvisoArriboDetalleXML"
                    oraCommandDocumentos.Parameters.Add("pNidDocumento", pNidDocumento)
                    oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pEncabezado", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pDatos", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pAnexos", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pIncidencias", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

                    Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
                    'pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
                    oraDataAdapter.Fill(dsDetalle)
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return dsDetalle
    End Function
  
	Public Function AvisoArribo_DatosBase() As DataSet
        Dim dtResultado As New DataSet("AvisoArribo_DatosBase")
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.AvisoArriboDatosBase"
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

                    Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
                    oraDataAdapter.Fill(dtResultado)
                    oraConnection.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return dtResultado
    End Function

    ''' <summary>
    ''' Funcion para generar un combo de la tabla AvisoArribo
    ''' </summary>
    ''' <param name="pNombre">nombre a buscar en la tabla</param>
    ''' <returns>DataTable</returns>
    ''' <remarks>si el nombre es "" retornara todos los registros</remarks>
	Public Function AvisoArribo_Combo(pNombre As String) As DataTable
        Dim dtResultado As New DataTable("AvisoArriboCombo")
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.AvisoArriboCombo"
                    oraCommandDocumentos.Parameters.Add("pNombre", pNombre)
                    oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

                    Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
                    oraDataAdapter.Fill(dtResultado)
                    oraConnection.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return dtResultado
    End Function

End Class
